package com.xgry.bpm.api.base.activiti.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xgry.bpm.api.base.activiti.service.ActivitiService;
import com.xgry.bpm.api.base.activiti.util.ProcessTaskUtil;
import com.xgry.bpm.api.base.formlist.entity.FormList;
import com.xgry.bpm.api.base.formlist.service.FormListService;
import com.xgry.bpm.api.base.reportparam.entity.ReportParam;
import com.xgry.bpm.api.base.reportparam.service.ReportParamService;
import com.xgry.bpm.api.base.sqldata.service.SqlData;
import com.xgry.bpm.api.base.tableclass.entity.TableClass;
import com.xgry.bpm.api.base.tableclass.service.TableClassService;
import com.xgry.bpm.api.base.userInfo.entity.UserInfo;
import com.xgry.bpm.api.base.userInfo.service.UserInfoService;
import com.xgry.bpm.common.enums.SystemEnum;
import com.xgry.bpm.common.utils.DateTimeUtil;
import com.xgry.bpm.common.utils.Result;
import com.xgry.bpm.config.datasouce.oldsource.DataDBServer;
import com.xgry.bpm.config.datasouce.oldsource.MSFactory;
import org.activiti.engine.HistoryService;
import org.activiti.engine.TaskService;
import org.activiti.engine.history.HistoricTaskInstance;
import org.activiti.engine.task.Comment;
import org.activiti.engine.task.Task;
import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.util.*;


/**
 * @author 作者：CN-xin
 * @version 创建时间：2019年4月4日 下午4:51:49
 * 类说明            历史信息查询  -----流程
 */
@RestController
@RequestMapping("/activitiTaskHisController")
public class ActivitiTaskHisController {
    @Autowired
    private TaskService taskService;
    @Autowired
    private FormListService formListService;
    @Autowired
    private SqlData sqlData;
    @Autowired
    private HistoryService historyService;
    @Resource
    private DataDBServer dbServer;
    @Autowired
    private UserInfoService userInfoService;
    @Autowired
    private TableClassService tableClassService;
    @Autowired
    private ReportParamService reportParamService;

    //@Autowired -- 原bpm未找见实现类
    private ActivitiService activitiService;





/*	public ArrayList findActList(Integer rows,Integer page, HashMap paramMap) {
		String sql = "";
		if(paramMap.get("checkmessage")!=null&&StringUtils.isNotEmpty(paramMap.get("checkmessage").toString())){
//			sql = "select * from  V_ACTLIST V where PROCINSTID= ? and clyj is not null and clyj <> '完成' ORDER BY START_TIME_";
			sql = "select * from  V_ACTLIST V where PROCINSTID= ? and clyj is not null ORDER BY START_TIME_";
		}else{
			sql = "SELECT V.*" + " FROM  V_ACTLIST V WHERE procInstId = ? ORDER BY V.START_TIME_";
		}
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(paramMap.get("procInstId").toString());


	}*/



/*	public int findActListCount(HashMap paramMap) {
		String sql = "";
		if(paramMap.get("checkmessage")!=null&&StringUtils.isNotEmpty(paramMap.get("checkmessage").toString())){
			sql = "select count(*) from  V_ACTLIST V where PROCINSTID= ? and clyj is not null and clyj <> '完成' ORDER BY START_TIME_";
		}else{
			sql = "SELECT count(*)" + " FROM  V_ACTLIST V WHERE procInstId = ? ORDER BY V.START_TIME_";
		}
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(paramMap.get("procInstId").toString());
		Session session = sessionFactory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		for (int i = 0; i < paramList.size(); i++) {
			query.setParameter(i, paramList.get(i));
		}
		List result = query.list();
		if (result.size() > 0) {
			return Integer.parseInt(result.get(0) == null ? "" : result.get(0).toString());
		}
		return 0;
	}*/


    @RequestMapping(value = "/queryActivitiList.do", produces = "application/json; charset=utf-8")
    public String queryActivitiList(HashMap<String, Object> paramMap, HttpSession session
            , String cTableName, String taskstatus,
                                    Integer rows, Integer page) throws Exception {

        JSONObject json = new JSONObject();
        // sql 参数
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        String userid = userInfo.getUserId().toString();
        List<Object> paramList = new ArrayList<Object>();
        String sql = "";

        System.out.println(cTableName);
        System.out.println(taskstatus);
        // 0.查询全部的任务
        if ("0".equals(taskstatus)) {
            sql = "SELECT * FROM (" + "SELECT tb.*,V.* FROM " + cTableName
                    + " tb left join  V_TASKLIST_DB V ON tb.processInstanceId = v.PROC_INST_ID_ WHERE (v.ASSIGNEE = '" + userid
                    + "' OR v.CANDIDATE = '" + userid + "') ";

            sql += " UNION SELECT tb.*,V.* FROM " + cTableName;


//			sql += " SELECT * FROM( SELECT tb.*,V.* FROM " + tableName
            sql += " tb left join  V_TASKLIST_YB V ON tb.processInstanceId = v.PROC_INST_ID_ WHERE  V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_YB B where B.ASSIGNEE = '"
                    + userid + "' group by b.PROC_INST_ID_ )  AND (v.ASSIGNEE = '" + userid + "' OR v.CANDIDATE = '"
                    + userid + "') ";


            sql += " UNION SELECT tb.*,V.* FROM " + cTableName
                    + " tb left join  V_TASKLIST_BJ V ON tb.processInstanceId = v.PROC_INST_ID_ WHERE v.ASSIGNEE = '" + userid
                    + "' AND V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_BJ B where B.ASSIGNEE = '" + userid
                    + "' group by b.PROC_INST_ID_ ) ";
            sql += " UNION SELECT tb.*,V.* FROM " + cTableName
                    + " tb left join  V_TASKLIST_WSB V ON tb.processInstanceId = v.PROC_INST_ID_ WHERE tb.state = 0 and tb.single_person='" + userid + "'";
            sql += " UNION SELECT tb.*,V.* FROM " + cTableName
                    + " tb left join  V_TASKLIST_CX V ON tb.processInstanceId = v.PROC_INST_ID_ WHERE v.ASSIGNEE = '" + userid
                    + "'";
            sql += ") t WHERE 1=1 ";

        }
        // 查询代办的任务
        if ("1".equals(taskstatus)) {
            sql = "SELECT * FROM " + cTableName
                    + " t left join  V_TASKLIST_DB V ON t.processInstanceId = v.PROC_INST_ID_  WHERE (v.ASSIGNEE = " + userid + " OR v.CANDIDATE = " + userid + ") ";
        }
        // 查询已办
        if ("2".equals(taskstatus)) {
            sql = "SELECT * FROM " + cTableName
                    + " t left join  V_TASKLIST_YB V ON t.processInstanceId = v.PROC_INST_ID_ WHERE (v.ASSIGNEE = " + userid + " OR v.CANDIDATE = " + userid + ") AND V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_YB B where B.ASSIGNEE = '"
                    + userid + "' group by b.PROC_INST_ID_ ) ";

        }
        // 查询办结的
        if ("3".equals(taskstatus)) {
            sql = "SELECT * FROM " + cTableName
                    + " t left join  V_TASKLIST_BJ V ON t.processInstanceId = v.PROC_INST_ID_ WHERE V.ASSIGNEE = " + userid + " AND V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_BJ B where B.ASSIGNEE = '"
                    + userid + "' group by b.PROC_INST_ID_ ) ";

        }
        // 查询办结的
        if ("4".equals(taskstatus)) {
            sql = "SELECT * FROM " + cTableName
                    + " t left join  V_TASKLIST_WSB V ON t.processInstanceId = v.PROC_INST_ID_ WHERE t.state = 0 ";
        }
        // 查询撤销的
        if ("5".equals(taskstatus)) {
            sql = "SELECT * FROM " + cTableName
                    + " t left join  V_TASKLIST_CX V ON t.processInstanceId = v.PROC_INST_ID_ WHERE V.ASSIGNEE = " + userid;
        }

        // 购置查询
        if ("6".equals(taskstatus)) {
            sql = "SELECT * FROM (";
            sql += " SELECT tb.*,V.* FROM " + cTableName
                    + " tb left join   V_TASKLIST_YB V ON tb.processInstanceId = v.PROC_INST_ID_ WHERE  (v.ASSIGNEE = '" + userid
                    + "' OR v.CANDIDATE = '" + userid + "') AND "
                    + " v.PROC_INST_ID_ Not IN(SELECT V.PROC_INST_ID_ FROM   V_TASKLIST_DB V WHERE (V.ASSIGNEE = '"
                    + userid + "' OR V.CANDIDATE = '" + userid
                    + "') ) AND V.BLTIME in ( SELECT max(bltime) from   V_TASKLIST_YB B where B.ASSIGNEE = '" + userid
                    + "' group by b.PROC_INST_ID_ ) ";
            sql += " UNION SELECT tb.*,V.* FROM " + cTableName
                    + " tb left join   V_TASKLIST_BJ V ON tb.processInstanceId = v.PROC_INST_ID_ WHERE v.ASSIGNEE = '" + userid
                    + "' AND V.BLTIME in ( SELECT max(bltime) from   V_TASKLIST_BJ B where B.ASSIGNEE = '" + userid
                    + "' group by b.PROC_INST_ID_ ) ";
            sql += ") t WHERE 1=1 ";

        }

        // 便利前台查询条件的paramMap
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            if (entry != null && entry.getValue() != null && !entry.getValue().equals("") && !entry.getValue().equals("null") && !entry.getKey().equals("null")) {
                sql += " AND  t." + entry.getKey() + " like ? ";
                paramList.add(entry.getValue());
            }
        }

        sql += " ORDER BY t.making_time DESC";

		/*Session session1 = sessionFactory.getCurrentSession();
		SQLQuery query = session1.createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		int firstNum = (page - 1) * rows;
		int lastNum = firstNum + rows;
		query.setFirstResult(firstNum);
		query.setMaxResults(lastNum);

		for (int i = 0; i < paramList.size(); i++) {
			query.setParameter(i, paramList.get(i));
		}
		return (ArrayList) query.list();*/
        int id = Integer.parseInt("1");
        ReportParam reportParam = reportParamService.getById(id);
        String dbUrl = "jdbc:sqlserver://" + reportParam.getIpAddress() + ":1433;DatabaseName=" + reportParam.getDbName();
        System.out.println(dbUrl);
        String dbDriver = reportParam.getJDBCDriver();
        System.out.println(dbDriver);
        String username = reportParam.getJDBCUserName();
        System.out.println(username);
        String password = reportParam.getJDBCPassword();
        System.out.println(password);
        ////////////////+++++++修改++++++++++//////////////////////
//		JDBCutils jdbCutils = new JDBCutils(dbDriver, dbUrl, username, password);
//		System.out.println(sql)；
// json.put("data", jdbCutils.excuteQuery(sql, null));

        JdbcTemplate jdbcTemplate = new MSFactory().getJdbcTemplate();
        json.put("data", jdbcTemplate.queryForList(sql));
        return json.toString();
    }

	/*
	public int findListCount(HashMap<String, Object> paramMap, String tableName, String userid, String taskstatus) {
		// sql 参数
		List<Object> paramList = new ArrayList<Object>();
		String sql = "";
		// 0.查询全部的任务
		if ("0".equals(taskstatus)) {
			sql = "SELECT count(*) FROM (" + "SELECT tb.*,V.* FROM " + tableName
					+ " tb left join  V_TASKLIST_DB V ON tb.procInstId = v.PROC_INST_ID_ WHERE (v.ASSIGNEE = '" + userid
					+ "' OR v.CANDIDATE = '" + userid + "') ";

			sql += " UNION SELECT tb.*,V.* FROM " + tableName
					+ " tb left join  V_TASKLIST_YB V ON tb.procInstId = v.PROC_INST_ID_ WHERE  V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_YB B where B.ASSIGNEE = '"
					+ userid + "' group by b.PROC_INST_ID_ )  AND (v.ASSIGNEE = '" + userid + "' OR v.CANDIDATE = '"
					+ userid + "') ";
			sql += " UNION SELECT tb.*,V.* FROM " + tableName
					+ " tb left join  V_TASKLIST_BJ V ON tb.procInstId = v.PROC_INST_ID_ WHERE v.ASSIGNEE = '" + userid
					+ "' AND V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_BJ B where B.ASSIGNEE = '" + userid
					+ "' group by b.PROC_INST_ID_ ) ";
			sql += " UNION SELECT tb.*,V.* FROM " + tableName
					+ " tb left join  V_TASKLIST_WSB V ON tb.procInstId = v.PROC_INST_ID_ WHERE tb.reportStatus = 0";
			sql += " UNION SELECT tb.*,V.* FROM " + tableName
					+ " tb left join  V_TASKLIST_CX V ON tb.procInstId = v.PROC_INST_ID_ WHERE v.ASSIGNEE = '" + userid
					+ "'";
			sql += ") t WHERE 1=1 ";

		}
		// 查询代办的任务
		if ("1".equals(taskstatus)) {
			sql = "SELECT count(*) FROM " + tableName
					+ " t left join  V_TASKLIST_DB V ON t.procInstId = v.PROC_INST_ID_  WHERE (v.ASSIGNEE = ? OR v.CANDIDATE = ?) ";
			paramList.add(userid);
			paramList.add(userid);
		}
		// 查询已办
		if ("2".equals(taskstatus)) {
			sql = "SELECT count(*) FROM " + tableName
					+ " t left join  V_TASKLIST_YB V ON t.procInstId = v.PROC_INST_ID_ WHERE (v.ASSIGNEE = ? OR v.CANDIDATE = ?) AND V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_YB B where B.ASSIGNEE = '"
					+ userid + "' group by b.PROC_INST_ID_ ) ";
			paramList.add(userid);
			paramList.add(userid);
		}
		// 查询办结的
		if ("3".equals(taskstatus)) {
			sql = "SELECT count(*) FROM " + tableName
					+ " t left join  V_TASKLIST_BJ V ON t.procInstId = v.PROC_INST_ID_ WHERE V.ASSIGNEE = ? AND V.BLTIME in ( SELECT max(bltime) from  V_TASKLIST_BJ B where B.ASSIGNEE = '"
					+ userid + "' group by b.PROC_INST_ID_ ) ";
			paramList.add(userid);
		}
		// 查询办结的
		if ("4".equals(taskstatus)) {
			sql = "SELECT count(*) FROM " + tableName
					+ " t left join  V_TASKLIST_WSB V ON t.procInstId = v.PROC_INST_ID_ WHERE t.reportStatus = 0 ";
		}
		// 查询撤销的
		if ("5".equals(taskstatus)) {
			sql = "SELECT count(*) FROM " + tableName
					+ " t left join  V_TASKLIST_CX V ON t.procInstId = v.PROC_INST_ID_ WHERE V.ASSIGNEE = ? ";
			paramList.add(userid);
		}

		// 购置查询
		if ("6".equals(taskstatus)) {
			sql = "SELECT count(*) FROM (";
			sql += " SELECT tb.*,V.* FROM " + tableName
					+ " tb left join   V_TASKLIST_YB V ON tb.procInstId = v.PROC_INST_ID_ WHERE  (v.ASSIGNEE = '" + userid
					+ "' OR v.CANDIDATE = '" + userid + "') AND "
					+ " v.PROC_INST_ID_ Not IN(SELECT V.PROC_INST_ID_ FROM   V_TASKLIST_DB V WHERE (V.ASSIGNEE = '"
					+ userid + "' OR V.CANDIDATE = '" + userid
					+ "') ) AND V.BLTIME in ( SELECT max(bltime) from   V_TASKLIST_YB B where B.ASSIGNEE = '" + userid
					+ "' group by b.PROC_INST_ID_ ) ";
			sql += " UNION SELECT tb.*,V.* FROM " + tableName
					+ " tb left join   V_TASKLIST_BJ V ON tb.procInstId = v.PROC_INST_ID_ WHERE v.ASSIGNEE = '" + userid
					+ "' AND V.BLTIME in ( SELECT max(bltime) from   V_TASKLIST_BJ B where B.ASSIGNEE = '" + userid
					+ "' group by b.PROC_INST_ID_ ) ";
			sql += ") t WHERE 1=1 ";

		}
		// 便利前台查询条件的paramMap
		for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
			if (entry!=null&&entry.getValue() != null && !entry.getValue().equals("")&& !entry.getValue().equals("null")&& !entry.getKey().equals("null")) {
				sql += " AND  t." + entry.getKey() + " like ? ";
				paramList.add(entry.getValue());
			}
		}

		Session session = sessionFactory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		for (int i = 0; i < paramList.size(); i++) {
			query.setParameter(i, paramList.get(i));
		}
		Object obj = query.uniqueResult();
		return  Integer.parseInt(obj.toString());
	}*/

    /**
     *
     *
     *
     * */
    /**
     * 待办流程分页查询
     * @param response
     * @param page 当前页数
     * @param rows 每页显示页数
     * @param s_name 流程名称
     * @param userId 流程ID
     * @return
     * @throws Exception
     */
	/*@RequestMapping(value = "/taskPage.do",produces = "application/json; charset=utf-8")
	@ResponseBody
	public String taskPage(HttpSession session,String page,String rows,String s_name,String userIds) {
		JSONObject json = new JSONObject();
		System.out.println("sessionId"+session.getId());
		UserInfo userInfo = (UserInfo)session.getAttribute(SystemEnum.SESSION_USER.getValue());
		if(s_name==null){
			s_name="";
		}
		//s_name = "店长审核";
		rows = "50";
		String userId = userInfo.getUserId().toString();
		PageInfo pageInfo=new PageInfo();
		Integer pageSize=Integer.parseInt(rows);
		pageInfo.setPageSize(pageSize);
		if(page==null||page.equals("")){
			page="1";
		}
		pageInfo.setPageIndex((Integer.parseInt(page)-1)*pageInfo.getPageSize());
		// 获取总记录数
		System.out.println("用户ID："+userInfo.getUserId()+"\n"+"名称:"+s_name);
		long total=taskService.createTaskQuery()
				.taskAssignee(userId)
				.orderByTaskCreateTime().desc()
				//.taskNameLike("%"+s_name+"%")
				.count(); // 获取总记录数
		//有想法的话，可以去数据库观察  ACT_RU_TASK 的变化
		List<Task> taskList=taskService.createTaskQuery()
				 // 根据用户id查询
				.taskAssignee(userId)
				// 根据任务名称查询
				//.taskNameLike("%"+s_name+"%")
				// 返回带分页的结果集合
				.orderByTaskCreateTime().desc()
				.listPage(pageInfo.getPageIndex(), pageInfo.getPageSize());
		//这里需要使用一个工具类来转换一下主要是转成JSON格式
		if(taskList != null && taskList.size() > 0) {
			List<MyTask> MyTaskList=new ArrayList<MyTask>();
			System.out.println(MyTaskList);
			for(Task t:taskList){
				System.out.println(t);
				//根据流程实例ID查询formlistId对应的数据
				System.out.println("流程实例ID"+t.getProcessDefinitionId());
				FormList formlist = formListService.findByProcessInstanceId(t.getProcessDefinitionId());
				//FormList formlist = formListService.findById(Integer.parseInt(t.getFormKey()));
				System.out.println("根据流程实例Id获取cTableName"+ formlist.getcTableName());
				String cTableName = formlist.getcTableName();
				Map<String, String> map = new HashMap<>();
				System.out.println(t.getId());
				map.put("processInstanceId", t.getExecutionId());
				System.out.println("节点Id"+t.getProcessDefinitionId()+"等于"+t.getExecutionId());
				System.out.println("根据cTableName和processInstanceId查询任务taskid");
				System.out.println("表名"+cTableName);
				String sql = sqlData.findByprocessInstanceId(cTableName, map);
				System.out.println(sql);
				JDBCutils j = JDBCuser.JDBCUser();
				List<Object> objs = j.excuteQuery(sql, null);
		    	JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(objs));
		    		System.out.println(jsonArray);
					JSONObject json1 = jsonArray.getJSONObject(0);
					String taskId = json1.getString("id");
				MyTask myTask=new MyTask();
				myTask.setId(t.getId());
				myTask.setName(t.getName());
				myTask.setCreateTime(t.getCreateTime());
				myTask.setcTableName(cTableName);
				myTask.setTaskId(taskId);
				myTask.setProcessId(t.getExecutionId());
				myTask.setFormlistId(Integer.toString(formlist.getFormListId()));
				MyTaskList.add(myTask);
		}
			json.put("data", MyTaskList);
			json.put("total", total);
			}else {
			json.put("data", null);
		}
		System.out.println(json.toString());
		return json.toString();
	}*/

    /**
     * 待办任务视图View查询
     */
    @RequestMapping(value = "/taskDBPage.do", produces = "application/json; charset=utf-8")
    public Result taskDBPage(HttpSession session, String page, String rows, String s_name, String userIds) throws Exception {
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        int id = Integer.parseInt("1");
        ReportParam reportParam = reportParamService.getById(id);
        String dbUrl = "jdbc:sqlserver://" + reportParam.getIpAddress() + ":1433;DatabaseName=" + reportParam.getDbName();
        System.out.println(dbUrl);
        String dbDriver = reportParam.getJDBCDriver();
        System.out.println(dbDriver);
        String username = reportParam.getJDBCUserName();
        System.out.println(username);
        String password = reportParam.getJDBCPassword();
        System.out.println(password);


        /////////////++++++修改+++++++++///////////////////
        //JDBCutils jdbCutils = new JDBCutils(dbDriver, dbUrl, username, password);
        JdbcTemplate jdbcTemplate = new MSFactory().getJdbcTemplate();

        String sql = "SELECT * FROM V_TASKLIST_DB where assignee =" + userInfo.getUserId();
        //List<Object> list = jdbCutils.excuteQuery(sql, null);

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(list));
        System.out.println(jsonArray);
        List<Map<String,Object>> result = new ArrayList<>();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject json1 = jsonArray.getJSONObject(i);
            String procinstId = json1.getString("PROC_INST_ID_");
            String endActId = json1.getString("END_ACT_ID_");
            String actName = json1.getString("actName");
            String actNameT = json1.getString("actName_T");
            String assignee = json1.getString("assignee");
            String candidate = json1.getString("candidate");
            String taskstatus = json1.getString("taskstatus");
            String bltime = json1.getString("bltime");
            String prdekey = json1.getString("prdekey");
            String prdename = json1.getString("prdename");
            String ProcessDefinitionId = json1.getString("ProcessDefinitionId");

            FormList formlist = formListService.findByProcessInstanceId(ProcessDefinitionId);
            System.out.println("根据流程实例Id获取cTableName" + formlist.getCTableName());
            String cTableName = formlist.getCTableName();
            Map<String, String> map = new HashMap<>();
            map.put("processInstanceId", ProcessDefinitionId);
            System.out.println("节点Id" + ProcessDefinitionId);
            System.out.println("根据cTableName和processInstanceId查询任务taskid");
            System.out.println("表名" + cTableName);
            String sql1 = sqlData.findByprocessInstanceId(cTableName, map);
            System.out.println(sql);
            JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(2);
            List<Map<String, Object>> objs = jdbcTemplate1.queryForList(sql);
            JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(objs));
            System.out.println(jsonArray1);
            JSONObject json2 = jsonArray.getJSONObject(0);
            String taskId = json1.getString("id");
            HashMap<String, Object> hashMap = new HashMap<>();
            hashMap.put("id",procinstId);
            hashMap.put("name",actName);
            try {
                hashMap.put("createTime",bltime);
            } catch (Exception e) {
                e.printStackTrace();
            }
            hashMap.put("cTableName",cTableName);
            hashMap.put("taskId",taskId);
            hashMap.put("formlistId",Integer.toString(formlist.getFormListId()));
            result.add(hashMap);
        }
        return Result.success(result);
    }


    /**
     * 已办任务视图View查询
     */
    @RequestMapping(value = "/taskYBPage.do", produces = "application/json; charset=utf-8")
    public Result taskYBPage(HttpSession session, String page, String rows, String s_name, String userIds) throws Exception {
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        JSONObject json = new JSONObject();
        String sql = "SELECT * FROM V_TASKLIST_YB where assignee =" + userInfo.getUserId();
        JdbcTemplate jdbcTemplate = new MSFactory().getJdbcTemplate();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(list));
        System.out.println(jsonArray);
        List<Map<String,Object>> result = new ArrayList<>();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject json1 = jsonArray.getJSONObject(i);
            String procinstId = json1.getString("PROC_INST_ID_");
            String taskId = json1.getString("taskId");
            String endActId = json1.getString("END_ACT_ID_");
            String actName = json1.getString("actName");
            String actNameT = json1.getString("actName_T");
            String assignee = json1.getString("assignee");
            String candidate = json1.getString("candidate");
            String taskstatus = json1.getString("taskstatus");
            String bltime = json1.getString("bltime");
            String prdekey = json1.getString("prdekey");
            String prdename = json1.getString("prdename");
            String ProcessDefinitionId = json1.getString("ProcessDefinitionId");

            FormList formlist = formListService.findByProcessInstanceId(ProcessDefinitionId);
            System.out.println("根据流程实例Id获取cTableName" + formlist.getCTableName());
            String cTableName = formlist.getCTableName();
            Map<String, String> map = new HashMap<>();
            map.put("processInstanceId", ProcessDefinitionId);
            System.out.println("节点Id" + ProcessDefinitionId);
            System.out.println("根据cTableName和processInstanceId查询任务taskid");
            System.out.println("表名" + cTableName);
            String sql1 = sqlData.findByprocessInstanceId(cTableName, map);
            System.out.println(sql);

            ///////////+++++修改+++++/////////////////////

            //JDBCutils j = JDBCuser.JDBCUser();

            //List<Object> objs = j.excuteQuery(sql, null);
            List<Map<String, Object>> objs = jdbcTemplate.queryForList(sql);
            JSONArray jsonArray1 = JSON.parseArray(JSON.toJSONString(objs));
            System.out.println(jsonArray1);
            JSONObject json2 = jsonArray.getJSONObject(0);
            String tasksId = json1.getString("id");
            HashMap<String, Object> hashMap = new HashMap<>();
            hashMap.put("id",taskId);
            hashMap.put("name",actName);
            try {
                hashMap.put("createTime",bltime);
            } catch (Exception e) {
                e.printStackTrace();
            }
            hashMap.put("cTableName",cTableName);
            hashMap.put("taskId",taskId);
            hashMap.put("formlistId",Integer.toString(formlist.getFormListId()));
            result.add(hashMap);
        }
        return Result.success(result);
    }


    /**
     * 已办结任务查询
     *
     *
     * */
	/*@RequestMapping(value = "/taskUnfinishPage.do",produces = "application/json; charset=utf-8")
	@ResponseBody
	public String taskUnfinishPage(HttpSession session,String page,String rows,String s_name,String userIds) {
		JSONObject json = new JSONObject();
		System.out.println("sessionId"+session.getId());
		UserInfo userInfo = (UserInfo)session.getAttribute(SystemEnum.SESSION_USER.getValue());
		if(s_name==null){
			s_name="";
		}
		s
		//s_name = "店长审核";
		rows = "50";
		String userId = userInfo.getUserId().toString();
		PageInfo pageInfo=new PageInfo();
		Integer pageSize=Integer.parseInt(rows);
		pageInfo.setPageSize(pageSize);
		if(page==null||page.equals("")){
			page="1";
		}
		pageInfo.setPageIndex((Integer.parseInt(page)-1)*pageInfo.getPageSize());
		// 获取总记录数
		System.out.println("用户ID："+userInfo.getUserId()+"\n"+"名称:"+s_name);
		long total=processEngine.getHistoryService() // 历史相关Service
	            .createHistoricTaskInstanceQuery() // 创建历史任务实例查询
	            .taskAssignee(userId)
	           // .processInstanceId("2501") // 用流程实例id查询
	            .unfinished() // 查询已经完成的任务
				.count(); // 获取总记录数
		//有想法的话，可以去数据库观察  ACT_RU_TASK 的变化
		System.out.println("总数"+total);
		List<HistoricTaskInstance> list=processEngine.getHistoryService() // 历史相关Service
	            .createHistoricTaskInstanceQuery() // 创建历史任务实例查询
	            .taskAssignee(userId)
	          //  .processInstanceId("2501") // 用流程实例id查询
	            .unfinished() // 查询已经完成的任务
	            .list();
		System.out.println("列表"+list);
		List<Task> taskList=taskService.createTaskQuery()
				 // 根据用户id查询
				.taskAssignee(userId)
				// 根据任务名称查询
				//.taskNameLike("%"+s_name+"%")
				// 返回带分页的结果集合
				.listPage(pageInfo.getPageIndex(), pageInfo.getPageSize());
		//这里需要使用一个工具类来转换一下主要是转成JSON格式
		List<MyTask> MyTaskList=new ArrayList<MyTask>();
		System.out.println(MyTaskList);
		for(HistoricTaskInstance t:list){
			System.out.println(t);
			System.out.println(t.getAssignee());
			System.out.println(t.getCategory());
			System.out.println(t.getDeleteReason());
			System.out.println(t.getDescription());
			System.out.println(t.getFormKey());
			System.out.println(t.getParentTaskId());
			System.out.println("getProcessDefinitionId"+t.getProcessDefinitionId());

			System.out.println(t.getOwner());
			System.out.println(t.getTaskDefinitionKey());
			System.out.println(t.getTaskDefinitionKey());
			System.out.println(t.getCreateTime());
			System.out.println(t.getEndTime());
			//根据流程实例ID查询formlistId对应的数据
			System.out.println("流程实例ID"+t.getProcessDefinitionId());
			FormList formlist = formListService.findByProcessInstanceId(t.getProcessDefinitionId());
			//FormList formlist = formListService.findById(Integer.parseInt(t.getFormKey()));
			System.out.println("根据流程实例Id获取cTableName"+ formlist.getcTableName());
			String cTableName = formlist.getcTableName();
			Map<String, String> map = new HashMap<>();
			System.out.println(t.getId());
			map.put("processInstanceId", t.getExecutionId());
			System.out.println("节点Id"+t.getProcessDefinitionId()+"等于"+t.getExecutionId());
			System.out.println("根据cTableName和processInstanceId查询任务taskid");
			System.out.println("表名"+cTableName);
			String sql = sqlData.findByprocessInstanceId(cTableName, map);
			System.out.println(sql);
			//jdbc连接池
			int id = Integer.parseInt("1");
			ReportParam reportParam = reportParamService.getById(id);
			String dbUrl = "jdbc:sqlserver://"+reportParam.getIpAddress()+":1433;DatabaseName="+reportParam.getDbName();
			System.out.println(dbUrl);
			String dbDriver = reportParam.getJDBCDriver();
			System.out.println(dbDriver);
			String username = reportParam.getJDBCUserName();
			System.out.println(username);
			String password = reportParam.getJDBCPassword();
			System.out.println(password);
			JDBCutils jdbCutils = new JDBCutils(dbDriver, dbUrl, username, password);


			//JDBCutils j = JDBCuser.JDBCUser();
			List<Object> objs = jdbCutils.excuteQuery(sql, null);
	    	com.alibaba.fastjson.JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(objs));
	    		System.out.println(jsonArray);
				JSONObject json1 = jsonArray.getJSONObject(0);
				String taskId = json1.getString("id");
			MyTask myTask=new MyTask();
			myTask.setId(t.getId());
			myTask.setName(t.getName());
			myTask.setCreateTime(t.getCreateTime());
			myTask.setEndTime(t.getEndTime());
			myTask.setcTableName(cTableName);
			myTask.setTaskId(taskId);
			myTask.setProcessId(t.getExecutionId());
			myTask.setFormlistId(Integer.toString(formlist.getFormListId()));
			MyTaskList.add(myTask);
		}
		json.put("data", MyTaskList);
		json.put("total", total);
		System.out.println(json.toString());
		return json.toString();
	}
*/


    /**
     * 已完成历史任务查询
     *
     *
     * */
/*	@RequestMapping(value = "/taskHisPage.do",produces = "application/json; charset=utf-8")
	@ResponseBody
	public String taskHisPage(HttpSession session,String page,String rows,String s_name,String userIds) {
		JSONObject json = new JSONObject();
		System.out.println("sessionId"+session.getId());
		UserInfo userInfo = (UserInfo)session.getAttribute(SystemEnum.SESSION_USER.getValue());
		if(s_name==null){
			s_name="";
		}
		//s_name = "店长审核";
		rows = "50";
		String userId = userInfo.getUserId().toString();
		PageInfo pageInfo=new PageInfo();
		Integer pageSize=Integer.parseInt(rows);
		pageInfo.setPageSize(pageSize);
		if(page==null||page.equals("")){
			page="1";
		}
		pageInfo.setPageIndex((Integer.parseInt(page)-1)*pageInfo.getPageSize());
		// 获取总记录数
		System.out.println("用户ID："+userInfo.getUserId()+"\n"+"名称:"+s_name);
		long total=processEngine.getHistoryService() // 历史相关Service
	            .createHistoricTaskInstanceQuery() // 创建历史任务实例查询
	            .taskAssignee(userId)
	           // .processInstanceId("2501") // 用流程实例id查询
	           .finished() // 查询已经完成的任务
	           .count(); // 获取总记录数

		//有想法的话，可以去数据库观察  ACT_RU_TASK 的变化
		System.out.println("总数"+total);
		List<HistoricTaskInstance> list=processEngine.getHistoryService() // 历史相关Service
	            .createHistoricTaskInstanceQuery() // 创建历史任务实例查询
	            .taskAssignee(userId)
	          //  .processInstanceId("2501") // 用流程实例id查询
	            .finished() // 查询已经完成的任务
	            .list();
		System.out.println("列表"+list);
		List<Task> taskList=taskService.createTaskQuery()
				 // 根据用户id查询
				.taskAssignee(userId)
				// 根据任务名称查询
				//.taskNameLike("%"+s_name+"%")
				// 返回带分页的结果集合
				.listPage(pageInfo.getPageIndex(), pageInfo.getPageSize());
		//这里需要使用一个工具类来转换一下主要是转成JSON格式
		List<MyTask> MyTaskList=new ArrayList<MyTask>();
		System.out.println(MyTaskList);
		for(HistoricTaskInstance t:list){
			System.out.println(t);
			System.out.println(t.getAssignee());
			System.out.println(t.getCategory());
			System.out.println(t.getDeleteReason());
			System.out.println(t.getDescription());
			System.out.println(t.getFormKey());
			System.out.println(t.getParentTaskId());
			System.out.println("getProcessDefinitionId"+t.getProcessDefinitionId());

			System.out.println(t.getOwner());
			System.out.println(t.getTaskDefinitionKey());
			System.out.println(t.getTaskDefinitionKey());
			System.out.println(t.getCreateTime());
			System.out.println(t.getEndTime());
			//根据流程实例ID查询formlistId对应的数据
			System.out.println("流程实例ID"+t.getProcessDefinitionId());
			FormList formlist = formListService.findByProcessInstanceId(t.getProcessDefinitionId());
			//FormList formlist = formListService.findById(Integer.parseInt(t.getFormKey()));
			System.out.println("根据流程实例Id获取cTableName"+ formlist.getcTableName());
			String cTableName = formlist.getcTableName();
			Map<String, String> map = new HashMap<>();
			System.out.println(t.getId());
			map.put("processInstanceId", t.getExecutionId());
			System.out.println("节点Id"+t.getProcessDefinitionId()+"等于"+t.getExecutionId());
			System.out.println("根据cTableName和processInstanceId查询任务taskid");
			System.out.println("表名"+cTableName);
			String sql = sqlData.findByprocessInstanceId(cTableName, map);
			System.out.println(sql);

			//jdbc连接池
			int id = Integer.parseInt("1");
			ReportParam reportParam = reportParamService.getById(id);
			String dbUrl = "jdbc:sqlserver://"+reportParam.getIpAddress()+":1433;DatabaseName="+reportParam.getDbName();
			System.out.println(dbUrl);
			String dbDriver = reportParam.getJDBCDriver();
			System.out.println(dbDriver);
			String username = reportParam.getJDBCUserName();
			System.out.println(username);
			String password = reportParam.getJDBCPassword();
			System.out.println(password);
			JDBCutils jdbCutils = new JDBCutils(dbDriver, dbUrl, username, password);
			//JDBCutils j = JDBCuser.JDBCUser2();
			List<Object> objs = jdbCutils.excuteQuery(sql, null);
	    	com.alibaba.fastjson.JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(objs));
	    		System.out.println(jsonArray);
				JSONObject json1 = jsonArray.getJSONObject(0);
				String taskId = json1.getString("id");
			MyTask myTask=new MyTask();
			myTask.setId(t.getId());
			myTask.setName(t.getName());
			myTask.setCreateTime(t.getCreateTime());
			myTask.setEndTime(t.getEndTime());
			myTask.setcTableName(cTableName);
			myTask.setTaskId(taskId);
			myTask.setProcessId(t.getExecutionId());
			myTask.setFormlistId(Integer.toString(formlist.getFormListId()));
			MyTaskList.add(myTask);
		}
		json.put("data", MyTaskList);
		json.put("total", total);
		System.out.println(json.toString());
		return json.toString();
	}*/


    /**
     * 带节点点亮的流程图
     *
     *
     * */


    /**
     * 审核记录查询
     *
     *
     * */


    /**
     * 查看所有任务
     */
    @RequestMapping(value = "/getAllTask.do", produces = "application/json; charset=utf-8")
    public Result getAllTask() throws Exception {
        JSONObject json = new JSONObject();
        List<Task> list = activitiService.getTasks();
        List<Map<String,Object>> result = new ArrayList<>();
        for (Task t : list) {
            System.out.println(t);
            //根据流程实例ID查询formlistId对应的数据
            System.out.println("流程实例ID" + t.getProcessDefinitionId());
            FormList formlist = formListService.findByProcessInstanceId(t.getProcessDefinitionId());
            //FormList formlist = formListService.findById(Integer.parseInt(t.getFormKey()));
            System.out.println("根据流程实例Id获取cTableName" + formlist.getCTableName());
            String cTableName = formlist.getCTableName();
            Map<String, String> map = new HashMap<>();
            System.out.println(t.getId());
            map.put("processInstanceId", t.getExecutionId());
            System.out.println("节点Id" + t.getProcessDefinitionId() + "等于" + t.getExecutionId());
            System.out.println("根据cTableName和processInstanceId查询任务taskid");
            System.out.println("表名" + cTableName);
            String sql = sqlData.findByprocessInstanceId(cTableName, map);
            System.out.println(sql);

            /////////////////修改///////////////////////////
            JdbcTemplate jdbcTemplate = new MSFactory().getJdbcTemplate();
            List<Map<String, Object>> objs = jdbcTemplate.queryForList(sql);
            //JDBCutils j = JDBCuser.JDBCUser();
            //List<Object> objs = j.excuteQuery(sql, null);


            JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(objs));
            System.out.println(jsonArray);
            JSONObject json1 = jsonArray.getJSONObject(0);
            String taskId = json1.getString("id");
            HashMap<String, Object> hashMap = new HashMap<>();
            hashMap.put("id",t.getId());
            hashMap.put("name",t.getName());
            hashMap.put("createTime",DateTimeUtil.dateToStr(t.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
            hashMap.put("cTableName",cTableName);
            hashMap.put("taskId",taskId);
            hashMap.put("formlistId",Integer.toString(formlist.getFormListId()));
            result.add(hashMap);
        }
        return Result.success(result);
    }


    /**
     * 根据登录号查询任务
     *
     * @param processDefinitionKey 流程定义key
     * @param loginName            登录号
     * @return
     */
    @RequestMapping(value = "/getTaskByLoginName.do", produces = "application/json; charset=utf-8")
    public String getTaskByLoginName(String processDefinitionKey, String loginName) {


        return null;
    }

    ;

    /**
     * 挂起流程实例
     */
    @RequestMapping(value = "/suspendProcessInstanceById.do", produces = "application/json; charset=utf-8")
    public Result suspendProcessInstanceById(String processInstanceId) {
        Boolean j = activitiService.suspendProcessInstanceById(processInstanceId);
        return Result.success(j?"挂起流程成功":"挂起流程失败");
    }

    ;

    /**
     * 恢复流程实例
     */
    @RequestMapping(value = "/activateProcessInstanceById.do", produces = "application/json; charset=utf-8")
    public Result activateProcessInstanceById(String processInstanceId) {
        Boolean j = activitiService.activateProcessInstanceById(processInstanceId);
        return Result.success(j?"恢复流程成功":"恢复流程失败");
    }

    ;

    /**
     * 委派任务
     */
    @RequestMapping(value = "/delegateTask.do")
    public Result delegateTask(String taskId, String userId) {
        taskId = "305052";
        userId = "9";
        Boolean j = activitiService.delegateTask(taskId, userId);
        return Result.success(j?"委托成功":"委托失败");
    }

    ;

    /**
     * 将任务委派他人完成
     *
     * @param taskId
     * @param @assignee
     * @return
     */
    @RequestMapping(value = "/trunTask.do", produces = "application/json; charset=utf-8")
    public Result trunTask(String taskId, String userId) {
        Boolean j = activitiService.trunTask(taskId, userId);
        return Result.success(j?"委托成功":"委托失败");
    }

    /**
     * 委派人完成任务
     *
     * @param taskId
     * @param map
     * @return
     */
    @RequestMapping(value = "/resolveTask.do", produces = "application/json; charset=utf-8")
    public Result resolveTask(String taskId, Map<String, Object> map) {
        Boolean j = activitiService.resolveTask(taskId, map);
        return Result.success(j?"委托人完成任务成功":"委托人完成任务失败");
    }

    ;

    /**
     * 查询历史流程批注
     *
     * @param response
     * @param processInstanceId 流程ID
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/listHistoryCommentWithProcessInstanceId.do", produces = "application/json; charset=utf-8")
    public Result listHistoryCommentWithProcessInstanceId(
            HttpServletResponse response, String processInstanceId) throws Exception {
        //测试数据
        //==============================================================
        //processInstanceId = "117501";
        //==============================================================
        if (processInstanceId == null) {
            return null;
        }
        List<Comment> commentList = taskService
                .getProcessInstanceComments(processInstanceId);
        // 改变顺序，按原顺序的反向顺序返回list
        Collections.reverse(commentList); //集合元素反转
        return Result.success(commentList);
    }


    /**
     * 查询历史批注
     *
     * @param response
     * @param taskId   流程ID
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/listHistoryComment.do", produces = "application/json; charset=utf-8")
    public Result listHistoryComment(HttpServletResponse response, String taskId) throws Exception {
        if (taskId == null) {
            taskId = "";
        }
        HistoricTaskInstance hti = historyService.createHistoricTaskInstanceQuery()
                .taskId(taskId)
                .singleResult();
        //JsonConfig jsonConfig = new JsonConfig();
        JSONObject result = new JSONObject();
        List<Comment> commentList = null;
        if (hti != null) {
            commentList = taskService.getProcessInstanceComments(hti.getProcessInstanceId());
            // 集合元素反转
            Collections.reverse(commentList);

            //日期格式转换
            //jsonConfig.registerJsonValueProcessor(Date.class, new DateJsonValueProcessor("yyyy-MM-dd hh:mm:ss"));
        }
        //JSONArray jsonArray=JSONArray.fromObject(commentList,jsonConfig);
        //result.put("data", jsonArray);
        //return result.toJSONString(result,SerializerFeature.DisableCircularReferenceDetect);
        return Result.success(commentList);
    }

    /**
     * 退回流程
     *
     * @param taskId     当前任务ID
     * @param activityId 退回节点ID
     * @param variables  流程存储参数
     * @throws Exception
     */
    @RequestMapping(value = "/backProcess.do", produces = "application/json; charset=utf-8")
    public String backProcess(HttpSession session, String taskId, String activityId, Map<String, Object> variables) throws Exception {
        taskId = "sid-DCB927A5-3253-4B13-9ACC-135D1B0DBD5C";
        activityId = "sid-4FD952F3-1B0C-47B7-8126-CE1627998F67";
        JSONObject json = new JSONObject();
        System.out.println(taskId);
        System.out.println(activityId);
        ProcessTaskUtil.backProcess(taskId, activityId, variables);
        return null;
    }


    /**
     * 办结任务查询
     */
    @RequestMapping(value = "/taskHisPage.do", produces = "application/json; charset=utf-8")
    public String taskHisPage(HttpSession session, String page, String rows, String s_name, String userIds) throws Exception {
        JSONObject json = new JSONObject();
        System.out.println("sessionId" + session.getId());
        UserInfo userInfo = (UserInfo) SecurityUtils.getSubject().getPrincipal();
        if (s_name == null) {
            s_name = "";
        }
        rows = "50";
        String userId = userInfo.getUserId().toString();
        String countSql = "select count(*) from \r\n" +
                "(SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                //"fl.formListId as formListId,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_BJ] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ \r\n" +  //and vb.NAME_ = 'formtaskId'\r\n
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')) t";
        String sql = "SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                //"fl.formListId as formListId,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_BJ] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ \r\n" +//and vb.NAME_ = 'formtaskId'\r\n"
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')";
        System.out.println(sql);
        JdbcTemplate jdbcTemplate = new MSFactory().getJdbcTemplate();
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(maps));
        List<Map<String,Object>> result = new ArrayList<>();
        if (jsonArray.size()>0) {
            for (int i = 0; i < jsonArray.size(); i++) {
                HashMap<String, Object> hashMap = new HashMap<>();
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                String processId = jsonObject.getString("processId");
                String cTableName = jsonObject.getString("cTableName");
                String createTime = jsonObject.getString("createTime");
                String name = jsonObject.getString("name");
                Map<String, String> map = new HashMap<>();
                map.put(cTableName + "_processInstanceId", processId);
                String byprocessInstanceId = sqlData.findByprocessInstanceId(cTableName, map);
                JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(2);
                List<Map<String, Object>> maps1 = jdbcTemplate1.queryForList(byprocessInstanceId);
                if (maps1.size() > 0) {
                    JSONArray jsonArray1 = JSONArray.parseArray(JSON.toJSONString(maps1));
                    if (jsonArray1.size() > 0) {
                            JSONObject jsonObject1 = jsonArray1.getJSONObject(0);
                            String uid = jsonObject1.getString(cTableName + "_single_person_id");
                            UserInfo byUserInfoId = null;
                            if (!uid.equals("") && uid != null) {
                                byUserInfoId = userInfoService.getById(Integer.parseInt(uid));
                                hashMap.put("name",name);
                                hashMap.put("taskId",jsonObject1.getString(cTableName + "_id"));
                                hashMap.put("processId",processId);
                                hashMap.put("createTime",createTime);
                                TableClass byTableClassName = tableClassService.findByTableClassName(cTableName);
                                hashMap.put("cTableName",byTableClassName.getTableChClassName());
                                if (byUserInfoId != null) {
                                    hashMap.put("explain",byUserInfoId.getUName() + "提交的" + byTableClassName.getTableChClassName());
                                }
                                result.add(hashMap);
                            }
                    }
                }
            }
        }
        json.put("MyTaskList", result);
        json.put("total", jdbcTemplate.queryForList(countSql));
        System.out.println(json.toString());
        return json.toString();
    }

    /**
     * 已办任务查询
     */
    @RequestMapping(value = "/taskUnfinishPage.do", produces = "application/json; charset=utf-8")
    @ResponseBody
    public String taskUnfinishPage(HttpSession session, String page, String rows, String s_name, String userIds) throws Exception {
        JSONObject json = new JSONObject();
        System.out.println("sessionId" + session.getId());
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        if (s_name == null) {
            s_name = "";
        }
        rows = "50";
        String userId = userInfo.getUserId().toString();
        String countSql = "select count(*) from \r\n" +
                "(SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "fl.formListId as formListId,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_YB] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ and vb.NAME_ = 'formtaskId'\r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')) t";
        String sql = "SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "fl.formListId as formListId,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_YB] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ and vb.NAME_ = 'formtaskId'\r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')";
        System.out.println("countSql:" + countSql);
        System.out.println("sql : " + sql);
        JdbcTemplate jdbcTemplate = new MSFactory().getJdbcTemplate();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(list));
        if (jsonArray.size()>0) {
            List<Map<String,Object>> myTaskList = new ArrayList<>();
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                String processId = jsonObject.getString("processId");
                String cTableName = jsonObject.getString("cTableName");
                String createTime = jsonObject.getString("createTime");
                String name = jsonObject.getString("name");
                Map<String, String> map = new HashMap<>();
                map.put(cTableName + "_processInstanceId", processId);
                String byprocessInstanceId = sqlData.findByprocessInstanceId(cTableName, map);
                JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(2);
                List<Map<String, Object>> maps1 = jdbcTemplate1.queryForList(byprocessInstanceId);
                if (maps1.size() > 0) {
                    JSONArray jsonArray1 = JSONArray.parseArray(JSON.toJSONString(maps1));
                    if (jsonArray1.size() > 0) {
                            JSONObject jsonObject1 = jsonArray1.getJSONObject(0);
                            String uid = jsonObject1.getString(cTableName + "_single_person_id");
                            UserInfo byUserInfoId = null;
                            if (!uid.equals("") && uid != null) {
                                byUserInfoId = userInfoService.getById(Integer.parseInt(uid));
                                Map<String,Object> hashMap = new HashMap<>();
                                hashMap.put("name",name);
                                hashMap.put("taskId",jsonObject1.getString(cTableName + "_id"));
                                hashMap.put("processId",processId);
                                hashMap.put("createTime",createTime);
                                TableClass byTableClassName = tableClassService.findByTableClassName(cTableName);
                                hashMap.put("cTableName",byTableClassName.getTableChClassName());
                                if (byUserInfoId != null) {
                                    hashMap.put("explain",byUserInfoId.getUName() + "提交的" + byTableClassName.getTableChClassName());
                                }
                                myTaskList.add(hashMap);
                        }
                    }
                }
            }
            json.put("MyTaskList", myTaskList);
        }
        json.put("data", jsonArray);
        json.put("total", jdbcTemplate.queryForList(countSql));
        System.out.println(json.toString());
        return json.toString();
    }


    /**
     * 待办任务查询
     */
    @RequestMapping(value = "/taskPage.do", produces = "application/json; charset=utf-8")
    @ResponseBody
    public String taskPage(HttpSession session, String page, String rows, String s_name, String userIds) throws Exception {
        JSONObject json = new JSONObject();
        System.out.println("sessionId" + session.getId());
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        if (s_name == null) {
            s_name = "";
        }
        rows = "50";
        String userId = userInfo.getUserId().toString();
        String countSql = "select count(*) from \r\n" +
                "(SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "fl.formListId as formListId,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_DB] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ and vb.NAME_ = 'formtaskId'\r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')) t";
        String sql = "SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "fl.formListId as formListId,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_DB] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ and vb.NAME_ = 'formtaskId'\r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')";
        int id = Integer.parseInt("1");
        ReportParam reportParam = reportParamService.getById(id);
        String dbUrl = "jdbc:sqlserver://" + reportParam.getIpAddress() + ":1433;DatabaseName=" + reportParam.getDbName();
        System.out.println(dbUrl);
        String dbDriver = reportParam.getJDBCDriver();
        System.out.println(dbDriver);
        String username = reportParam.getJDBCUserName();
        System.out.println(username);
        String password = reportParam.getJDBCPassword();
        System.out.println(password);

        ////////////+++++++++修改++++++++++++///////////////////////////
        JdbcTemplate jdbcTemplate = new MSFactory().getJdbcTemplate();

        //JDBCutils jdbCutils = new JDBCutils(dbDriver, dbUrl, username, password);
        //json.put("data", jdbCutils.excuteQuery(sql, null));
        json.put("data", jdbcTemplate.queryForList(sql));
        //json.put("total",jdbCutils.excuteQuery(countSql, null));
        json.put("total", jdbcTemplate.queryForList(countSql));
        System.out.println(json.toString());
        return json.toString();
    }

    /**
     * 全部任务查询
     */
    @RequestMapping(value = "/taskAllPage.do", produces = "application/json; charset=utf-8")
    @ResponseBody
    public String taskAllPage(HttpSession session, String page, String rows, String s_name, String userIds) throws Exception{
        JSONObject json = new JSONObject();
        System.out.println("sessionId" + session.getId());
        UserInfo userInfo = (UserInfo) session.getAttribute(SystemEnum.SESSION_USER.getValue());
        if (s_name == null) {
            s_name = "";
        }
        rows = "50";
        String userId = userInfo.getUserId().toString();
        String countSql = "select count(*) from \r\n" +
                "(SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_BJ] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_\r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')" +
                " UNION " +
                "SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_YB] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_\r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')) t";
        String sql = "SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_BJ] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ \r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')" +
                " UNION " +
                "SELECT \r\n" +
                "fl.cTableName AS cTableName,\r\n" +
                "yb.bltime as createTime,\r\n" +
                "yb.taskId as id,\r\n" +
                "yb.actName as name,\r\n" +
                "yb.PROC_INST_ID_ as processId,\r\n" +
                "vb.TEXT_ as taskId\r\n" +
                "FROM [dbo].[V_TASKLIST_YB] yb\r\n" +
                "left join ACT_RU_VARIABLE vb\r\n" +
                "on yb.PROC_INST_ID_ = vb.PROC_INST_ID_ \r\n" +
                "left join form_list fl\r\n" +
                "on yb.ProcessDefinitionId = fl.processInstanceId\r\n" +
                "where\r\n" +
                "(yb.ASSIGNEE = '" + userId + "' OR yb.CANDIDATE = '" + userId + "')";
        JdbcTemplate jdbcTemplate =  new MSFactory().getJdbcTemplate();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(list));
        if (jsonArray.size()>0) {
            List<Map<String,Object>> myTaskList = new ArrayList<>();
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                String processId = jsonObject.getString("processId");
                String cTableName = jsonObject.getString("cTableName");
                String createTime = jsonObject.getString("createTime");
                String name = jsonObject.getString("name");
                Map<String, String> map = new HashMap<>();
                map.put(cTableName + "_processInstanceId", processId);
                String byprocessInstanceId = sqlData.findByprocessInstanceId(cTableName, map);
                JdbcTemplate jdbcTemplate1 = dbServer.getJdbcTemplate(2);
                List<Map<String, Object>> maps1 = jdbcTemplate1.queryForList(byprocessInstanceId);
                if (maps1.size() > 0) {
                    JSONArray jsonArray1 = JSONArray.parseArray(JSON.toJSONString(maps1));
                    if (jsonArray1.size() > 0) {
                            JSONObject jsonObject1 = jsonArray1.getJSONObject(0);
                            String uid = jsonObject1.getString(cTableName + "_single_person_id");
                            UserInfo byUserInfoId = null;
                            if (!uid.equals("") && uid != null) {
                                byUserInfoId = userInfoService.getById(Integer.parseInt(uid));
                                HashMap<String, Object> hashMap = new HashMap<>();
                                hashMap.put("name",name);
                                hashMap.put("taskId",jsonObject1.getString(cTableName + "_id"));
                                hashMap.put("processId",processId);
                                hashMap.put("createTime",createTime);
                                TableClass byTableClassName = tableClassService.findByTableClassName(cTableName);
                                hashMap.put("cTableName",byTableClassName.getTableChClassName());
                                if (byUserInfoId != null) {
                                    hashMap.put("processId",byUserInfoId.getUName() + "提交的" + byTableClassName.getTableChClassName());
                                }
                                myTaskList.add(hashMap);
                            }
                    }
                }
            }
            json.put("MyTaskList", myTaskList);
        }
        json.put("data", jdbcTemplate.queryForList(sql));
        json.put("total", jdbcTemplate.queryForList(countSql));
        System.out.println(json.toString());
        return json.toString();
    }


}
